SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





ALTER           VIEW dbo.V_EmpMaster

AS

SELECT td_EmpMaster.*,
               DATEDIFF(YEAR,EM_Birthday,GETDATE())  AS EmpAge,
               case when datediff(year,EM_HereDate,getdate()) = 0 then 0 
                  else datediff(year,EM_HereDate,getdate())-1 +   case when datepart(month,EM_HereDate) < 7 and datepart(month,getdate()) >= 7 then 1 else 0 end
               end AS HereYears,
               B.ED_DeptNM,C.EG_GroupNM,E.PC_ProvinceNM,
	  ISNULL(PC_ProvinceNM,'')+ISNULL(EM_Address,'') as EmpAddress,TP_TPostName,p.ep_postnm
FROM     td_EmpMaster
	  LEFT JOIN td_Dept 		AS B ON(td_EmpMaster.DeptID=B.ED_DeptID)
	  LEFT JOIN td_Group		AS C ON(td_EmpMaster.EM_GroupID=C.EG_GroupID AND td_EmpMaster.DeptID=C.EG_DeptID)
	  LEFT JOIN td_ProvinceCode 	AS E ON(td_EmpMaster.EM_ProvinceNo=E.PC_ProvinceNo)
	  LEFT JOIN td_TechnicalPost	AS I ON(td_EmpMaster.EM_TechnicalPost=I.TP_TPostID)
          Left JOIN td_post             AS P ON(td_EmpMaster.em_post=p.ep_postid)














GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

